Open topic with navigation

Database reference guide

 HOME

Miscellaneous Functions

The following are text column functions:

DISTINCT ISNULL
PAREA ROW
PDIST PSECT
SORTFORMAT

ISNULL Function

Returns true for Null values or false for non-Null.

The parameter is the name of a column of any data type.

Return Type

Boolean.

Remarks
Note: This is the only way that NULL values can be included in a search. The syntax "X = NULL", which worked in earlier versions of Engine, is no longer supported and will always return false.
Syntax
Row(string)

Returns the row number from the column given for each value.

The string parameter is the name of any column in the database.

Returns

Integer

Remarks

Can also be called as IURN(<column>).

DISTINCT Function

Syntax
Distinct(string)

For each value, this returns its offset into the index unique list.

The string parameter is the name of any column in the database.

Returns

Integer

Special function for Text columns containing US zip codes/UK post code fields:  

PAREA Function

Syntax
Parea(string)

Returns the Postal Area from a UK-Style postcode.

The string parameter is a string constant or the name of a column containing UK postcodes.

Returns

String

Remarks

The postal area is the first 2 letters, or the first letter if the 2nd character is a number.

Example

Select parea([Demo].[Person].[Postcode]) from [Demo].[Person]

Returns

“CF” for “CF64 1AU”

“B” for “B1 8NN”

PDIST Function

Pdist(string)

Returns the Postal District from a UK-Style postcode.

The string parameter is a string constant or the name of a column containing UK postcodes.

Returns

String

Remarks

The Postal District is the postcode minus the last three chars, with all spaces removed.

Example
Select pdist([Demo].[Person].[Postcode]) from [Demo].[Person]

Returns

“PO1” for “PO1 3AX”

Psect(string)

Returns the Postal Sector from a UK-Style postcode.

The string parameter is a string constant or the name of a column containing UK postcodes.

Returns

String

Example

Select psect([Demo].[Person].[Postcode]) from [Demo].[Person]

Returns

“PO13” for “PO1 3AX”

SORTFORMAT (<column>) - converts the postcode to Sort Format.

  Online & Instructor-Led Courses | Training Videos | Webinar Recordings
© Alterian. All Rights Reserved. | Privacy Policy | Legal Notice